import traceback

from backend.app.app.api.api_Xiong.crud.pub_crud import dvpunit_name_tree, stimtype_tree
from backend.app.app.db.session_project import session_pro
import backend.app.app.models.db_owi_private_models as md_pro
import backend.app.app.models.db_owi_public_models as md_pub
from sqlalchemy import desc
from backend.app.app.db.session_public import session_pub


def get_proId_by_user_id(user_id):
    try:
        project_inf = session_pro.query(md_pro.TbUserprojDvpunitOilstimevalInput.Proj_ID)\
            .filter(md_pro.TbUserprojDvpunitOilstimevalInput.User_ID == user_id
                    )\
            .order_by(desc(md_pro.TbUserprojDvpunitOilstimevalInput.Proj_ID)).all()

        return project_inf
    except BaseException:
        print('查找数据失败')
        session_pro.rollback()
        return False

#   根据user_id,pro_id获取名字，类型，年度


def get_name(user_id, pro_id):
    try:
        project_inf = session_pro.query(md_pro.TbUserprojDvpunitOilstimevalInput.DvpUnit_Name  # 单元名称
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Year  # 年度
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.StimType_Name  # 措施类别
                                        )\
            .filter(md_pro.TbUserprojDvpunitOilstimevalInput.User_ID == user_id, md_pro.TbUserprojDvpunitOilstimevalInput.Proj_ID == pro_id
                    )\
            .order_by(md_pro.TbUserprojDvpunitOilstimevalInput.DvpUnit_Name,
                      md_pro.TbUserprojDvpunitOilstimevalInput.StimType_Name,
                      md_pro.TbUserprojDvpunitOilstimevalInput.Year).all()

        return project_inf
    except BaseException:
        print('输入数据查找数据失败')
        session_pro.rollback()
        return False

# 根据单元名称获取所有输入界面信息,开发单元，类别名称，年份


def get_query_data_by_id(user_id, pro_id):
    try:
        project_inf = session_pro.query(md_pro.TbUserprojDvpunitOilstimevalInput.User_ID,
                                        md_pro.TbUserprojDvpunitOilstimevalInput.Proj_ID, md_pro.TbUserprojDvpunitOilstimevalInput.DvpUnit_Name  # 单元名称
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Year  # 年度
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.StimType_Name  # 措施类别
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Stim_WellNumber_Valid  # 有效井次
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Stim_WellValidRatio  # 有效率
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Stim_ValidPeriod_Average  # 平均有效期
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Stim_IncreOil_Average  # 平均措施增油
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.StimCost_Average  # 平均作业成本
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Oil_OprCost  # 吨油操作成本
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Oil_Price  # 平均油价无，此处读取为原油价格
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.StimOil_CommodityRatio  # 原油商品率
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.OilSale_TotalTaxRatio  # 平均销售税费率无，此处读取销售税费率
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Money_Discount  # 资金贴现率
                                        , md_pro.TbUserprojDvpunitOilstimevalInput.Remark  # 备注
                                        )\
            .filter(md_pro.TbUserprojDvpunitOilstimevalInput.User_ID == user_id, md_pro.TbUserprojDvpunitOilstimevalInput.Proj_ID == pro_id
                    )\
            .order_by(md_pro.TbUserprojDvpunitOilstimevalInput.DvpUnit_Name,
                      md_pro.TbUserprojDvpunitOilstimevalInput.StimType_Name,
                      md_pro.TbUserprojDvpunitOilstimevalInput.Year).all()

        return project_inf
    except BaseException:
        print('输入数据查找数据失败')
        session_pro.rollback()
        return False

# 根据单元名称获取所有输入界面信息,开发单元，类别名称，年份


def get_query_data(dvpUnit_Name, stimType_Name, year):
    project_inf = dict()
    dvpUnit_Name = dvpUnit_Name[0]
    stimType_Name = stimType_Name[0]
    if dvpUnit_Name and stimType_Name and year:
        project_inf['DvpUnit_Name'] = dvpUnit_Name
        project_inf['StimType_Name'] = stimType_Name
        project_inf['Year'] = year
    try:
        # project_inf['StimCost_Average'] = None
        # stim_cost_average=session_pub.query(
        #     md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimCost_Average
        #     ).filter(
        #     md_pub.TbYearDvpunitOilstimulateEvaluateResult.DvpUnit_Name == dvpUnit_Name,
        #     md_pub.TbYearDvpunitOilstimulateEvaluateResult.StimType_Name == stimType_Name,
        #     md_pub.TbYearDvpunitOilstimulateEvaluateResult.Year == year
        # ).first()
        #
        # if stim_cost_average:
        #     project_inf.update({'StimCost_Average':stim_cost_average[0]})

        stim_cost = session_pub.query(
            md_pub.TbYearDvpunitOilstimCost.Stim_Cost  # 作业费用
        ).filter(
            md_pub.TbYearDvpunitOilstimCost.DvpUnit_Name == dvpUnit_Name,
            md_pub.TbYearDvpunitOilstimCost.StimType_Name == stimType_Name,
            md_pub.TbYearDvpunitOilstimCost.Year == year
        ).first()
        project_inf['Stim_Cost'] = None
        if stim_cost:
            project_inf.update({'Stim_Cost': stim_cost[0]})

        price = session_pub.query(
            md_pub.TbYearDvpunitOilprice.Oil_CommodityRatio,  # 原油商品率
            md_pub.TbYearDvpunitOilprice.Oil_Price  # 原油价格
        ).filter(
            md_pub.TbYearDvpunitOilprice.DvpUnit_Name == dvpUnit_Name,
            md_pub.TbYearDvpunitOilprice.Year == year
        ).first()
        project_inf['Oil_CommodityRatio'] = None
        project_inf['Oil_Price'] = None
        if price:
            data = dict(zip(price.keys(), price))
            project_inf.update(
                {'Oil_CommodityRatio': data['Oil_CommodityRatio']})
            project_inf.update({'Oil_Price': data['Oil_Price']})

        prod_cost = session_pub.query(
            md_pub.TbYearDvpunitOilprodcost.Oil_OprCost  # 操作吨油成本
        ).filter(
            md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name == dvpUnit_Name,
            md_pub.TbYearDvpunitOilprodcost.Year == year
        ).first()
        project_inf['Oil_OprCost'] = None
        if prod_cost:
            project_inf.update({'Oil_OprCost': prod_cost[0]})

        sale_cost_ratio = session_pub.query(
            md_pub.TbYearDvpunitOilsalecostRatio.OilSale_TotalTaxRatio  # 销售税费率
        ).filter(
            md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name == dvpUnit_Name,
            md_pub.TbYearDvpunitOilsalecostRatio.Year == year
        ).first()
        project_inf['OilSale_TotalTaxRatio'] = None
        if sale_cost_ratio:
            project_inf.update({'OilSale_TotalTaxRatio': sale_cost_ratio[0]})

        discount = session_pub.query(
            md_pub.TbYearDvpunitDiscount.Money_Discount  # 资金贴现率
        ).filter(
            md_pub.TbYearDvpunitDiscount.DvpUnit_Name == dvpUnit_Name,
            md_pub.TbYearDvpunitDiscount.Year == year
        ).first()
        project_inf['Money_Discount'] = None
        if discount:
            project_inf['Money_Discount'] = discount[0]

        plan_proj = session_pub.query(
            md_pub.TbYearDvpunitOilstimResult.Stim_WellValidRatio,  # 有效率
            md_pub.TbYearDvpunitOilstimResult.Stim_WellNumber_Valid,  # 有效井次
            md_pub.TbYearDvpunitOilstimResult.Stim_ValidPeriod_Average,  # 平均有效期
            md_pub.TbYearDvpunitOilstimResult.Stim_IncreOil_Average  # 平均增油量
        ).filter(
            md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name == dvpUnit_Name,
            md_pub.TbYearDvpunitOilstimResult.StimType_Name == stimType_Name,
            md_pub.TbYearDvpunitOilstimResult.Year == year,
        ).first()
        project_inf['Stim_WellValidRatio'] = None
        project_inf['Stim_WellNumber_Valid'] = None
        project_inf['Stim_ValidPeriod_Average'] = None
        project_inf['Stim_IncreOil_Average'] = None
        if plan_proj:
            data = dict(zip(plan_proj.keys(), plan_proj))
            project_inf.update(
                {'Stim_WellValidRatio': data['Stim_WellValidRatio']})
            project_inf.update(
                {'Stim_WellNumber_Valid': data['Stim_WellNumber_Valid']})
            project_inf.update(
                {'Stim_ValidPeriod_Average': data['Stim_ValidPeriod_Average']})
            project_inf.update(
                {'Stim_IncreOil_Average': data['Stim_IncreOil_Average']})
        return project_inf

    except Exception as e:
        print('输入数据查找数据失败', e)
        session_pub.rollback()
        return False

# 根据条件查询税费明细


def query_ratio(dvpunit_name, start_year, end_year):
    try:
        project_inf = session_pub.query(
            md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name,
            md_pub.TbYearDvpunitOilsalecostRatio.Year,
            md_pub.TbYearDvpunitOilsalecostRatio.OilSale_TotalTaxRatio,
            md_pub.TbYearDvpunitOilsalecostRatio.AddedValue_TaxRatio,
            md_pub.TbYearDvpunitOilsalecostRatio.Resource_TaxRatio,
            md_pub.TbYearDvpunitOilsalecostRatio.EduAdd_TaxRatio,
            md_pub.TbYearDvpunitOilsalecostRatio.CityDvpAdd_TaxRate,
            md_pub.TbYearDvpunitOilsalecostRatio.Income_TaxRate,
            md_pub.TbYearDvpunitOilsalecostRatio.ManageCost_Ratio,
            md_pub.TbYearDvpunitOilsalecostRatio.Remark
        ).filter(
            md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name.in_(dvpunit_name),
            md_pub.TbYearDvpunitOilsalecostRatio.Year >= start_year,
            md_pub.TbYearDvpunitOilsalecostRatio.Year <= end_year
        ).order_by(
            md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name,
            md_pub.TbYearDvpunitOilsalecostRatio.Year
        ).all()
        project_inf = [dict(zip(data.keys(), data)) for data in project_inf]
        return project_inf
    except BaseException:
        print('输入数据查找数据失败')
        session_pub.rollback()
        return False


# 根据条件查询原油操作成本明细
def query_oil(dvpunit_name, start_year, end_year):
    try:
        project_inf = session_pub.query(
            md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name,
            md_pub.TbYearDvpunitOilprodcost.Year,
            md_pub.TbYearDvpunitOilprodcost.Oil_OprCost,
            md_pub.TbYearDvpunitOilprodcost.FuelCost,
            md_pub.TbYearDvpunitOilprodcost.Power_Cost,
            md_pub.TbYearDvpunitOilprodcost.FluidDispose_Cost,
            md_pub.TbYearDvpunitOilprodcost.OilDisplaceAgent_Cost,
            md_pub.TbYearDvpunitOilprodcost.NGLRecovery_Cost,
            md_pub.TbYearDvpunitOilprodcost.Transp_Cost,
            md_pub.TbYearDvpunitOilprodcost.WellTest_Cost,
            md_pub.TbYearDvpunitOilprodcost.Other_Cost,
            md_pub.TbYearDvpunitOilprodcost.Depre_Cost,
            md_pub.TbYearDvpunitOilprodcost.Remark
        ) .filter(
                md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name.in_(dvpunit_name),
                md_pub.TbYearDvpunitOilprodcost.Year >= start_year,
                md_pub.TbYearDvpunitOilprodcost.Year <= end_year
            ) .order_by(
                    md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name,
                    md_pub.TbYearDvpunitOilprodcost.Year
                 ).all()
        project_inf = [dict(zip(data.keys(), data)) for data in project_inf]
        return project_inf
    except BaseException:
        print('输入数据查找数据失败')
        session_pub.rollback()
        return False

# 根据单元名称获取所有输入界面信息,开发单元，类别名称，年份


def get_all_data(dvpUnit_Name, stimType_Name, start_year, end_year):
    try:
        temp_results = session_pub.query(
            md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name,
            md_pub.TbYearDvpunitOilstimResult.StimType_Name,
            md_pub.TbYearDvpunitOilstimResult.Year
        ).filter(
            md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name.in_(dvpUnit_Name),
            md_pub.TbYearDvpunitOilstimResult.Year <= end_year,
            md_pub.TbYearDvpunitOilstimResult.Year >= start_year,
            md_pub.TbYearDvpunitOilstimResult.StimType_Name.in_(stimType_Name)
        ).all()
        temp_result_dict = [dict(zip(result.keys(), result))
                            for result in temp_results]
        result_data = None
        if temp_result_dict:
            results = list()
            for result in temp_result_dict:
                temp_result = session_pub.query(
                    md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name,
                    md_pub.TbYearDvpunitOilstimResult.Year,
                    md_pub.TbYearDvpunitOilstimResult.StimType_Name,
                    md_pub.TbYearDvpunitOilstimResult.Stim_WellNumber_Valid,  # 有效井次
                    md_pub.TbYearDvpunitOilstimResult.Stim_WellValidRatio,  # 有效率
                    md_pub.TbYearDvpunitOilstimResult.Stim_ValidPeriod_Average,  # 平均有有效期
                    md_pub.TbYearDvpunitOilstimResult.Stim_IncreOil_Average,  # 平均累计增油量

                    md_pub.TbYearDvpunitOilstimCost.Stim_Cost,  # 作业费用

                    md_pub.TbYearDvpunitOilprice.Oil_CommodityRatio,  # 原油商品率
                    md_pub.TbYearDvpunitOilprice.Oil_Price,  # 原油价格

                    md_pub.TbYearDvpunitDiscount.Money_Discount  # 资金贴现率
                ).filter(
                    md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilstimResult.Year == result['Year'],
                    md_pub.TbYearDvpunitOilstimResult.StimType_Name == result['StimType_Name'],

                    md_pub.TbYearDvpunitOilstimCost.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilstimCost.Year == result['Year'],
                    md_pub.TbYearDvpunitOilstimCost.StimType_Name == result['StimType_Name'],

                    md_pub.TbYearDvpunitOilprice.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilprice.Year == result['Year'],

                    md_pub.TbYearDvpunitDiscount.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitDiscount.Year == result['Year']
                ).first()
                if temp_result:
                    results.append(temp_result)
            result_data = [dict(zip(result.keys(), result))
                           for result in results]
        return result_data
    except Exception as e:
        print('输入数据查找数据失败', e)
        session_pub.rollback()
        return False


#   存库 原油销售税费率表
# hzc改
class year_dvpunit_oilsalecost_ratio():
    return_data = list()

    def updata(self, data):
        try:
            session_pub.query(md_pub.TbYearDvpunitOilsalecostRatio). \
                filter(
                md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name == data["DvpUnit_Name"],
                md_pub.TbYearDvpunitOilsalecostRatio.Year == data["Year"]).update(
                {
                    md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name: data["DvpUnit_Name"],
                    md_pub.TbYearDvpunitOilsalecostRatio.Year: data["Year"],
                    md_pub.TbYearDvpunitOilsalecostRatio.OilSale_TotalTaxRatio: data["OilSale_TotalTaxRatio"],
                    md_pub.TbYearDvpunitOilsalecostRatio.AddedValue_TaxRatio: data["AddedValue_TaxRatio"],
                    md_pub.TbYearDvpunitOilsalecostRatio.Resource_TaxRatio: data["Resource_TaxRatio"],
                    md_pub.TbYearDvpunitOilsalecostRatio.EduAdd_TaxRatio: data["EduAdd_TaxRatio"],
                    md_pub.TbYearDvpunitOilsalecostRatio.CityDvpAdd_TaxRate: data["CityDvpAdd_TaxRate"],
                    md_pub.TbYearDvpunitOilsalecostRatio.Income_TaxRate: data["Income_TaxRate"],
                    md_pub.TbYearDvpunitOilsalecostRatio.ManageCost_Ratio: data["ManageCost_Ratio"],
                    md_pub.TbYearDvpunitOilsalecostRatio.Remark: data["Remark"]
                }
            )
            session_pub.commit()
            print('更新输入数据成功')
            return True
        except BaseException:
            print('更新输入数据失败')
            session_pub.rollback()
            return False

    def delete(self, id):
        try:
            session_pub.query(md_pub.TbYearDvpunitOilsalecostRatio) \
                .filter(md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name == id["DvpUnit_Name"],
                        md_pub.TbYearDvpunitOilsalecostRatio.Year == id["Year"]).delete()
            session_pub.commit()
            print('删除数据成功')
            return True
        except BaseException:
            print('删除数据失败')
            session_pub.rollback()
            return False

    def add(self, data):
        new_data = md_pub.TbYearDvpunitOilsalecostRatio(
            DvpUnit_Name=data["DvpUnit_Name"],
            Year=data["Year"],
            OilSale_TotalTaxRatio=data["OilSale_TotalTaxRatio"],
            AddedValue_TaxRatio=data["AddedValue_TaxRatio"],
            Resource_TaxRatio=data["Resource_TaxRatio"],
            EduAdd_TaxRatio=data["EduAdd_TaxRatio"],
            CityDvpAdd_TaxRate=data["CityDvpAdd_TaxRate"],
            Income_TaxRate=data["Income_TaxRate"],
            ManageCost_Ratio=data["ManageCost_Ratio"],
            Remark=data["Remark"]
        )
        try:
            session_pub.add(new_data)
            session_pub.commit()
            print('存入数据成功')
            session_pub.close()
            return True
        except BaseException:
            print('存入数据失败')
            session_pub.rollback()
            return False

# hzc改


def oilsalecost_ratio(radio_data):
    return_data = list()
    for data in radio_data:
        try:
            session_pub.query(md_pub.TbYearDvpunitOilsalecostRatio).filter(
                md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilsalecostRatio.Year == data['Year']
            ).delete()
            add_data = md_pub.TbYearDvpunitOilsalecostRatio(
                DvpUnit_Name=data["DvpUnit_Name"],
                Year=data["Year"],
                OilSale_TotalTaxRatio=data["OilSale_TotalTaxRatio"],
                AddedValue_TaxRatio=data["AddedValue_TaxRatio"],
                Resource_TaxRatio=data["Resource_TaxRatio"],
                EduAdd_TaxRatio=data["EduAdd_TaxRatio"],
                CityDvpAdd_TaxRate=data["CityDvpAdd_TaxRate"],
                Income_TaxRate=data["Income_TaxRate"],
                ManageCost_Ratio=data["ManageCost_Ratio"],
                Remark=data["Remark"]
            )
            session_pub.add(add_data)
            session_pub.commit()
            return_data.append(
                {
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'result': True
                }
            )

        except Exception as e:
            return_data.append(
                {
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'result': False
                }
            )
            session_pub.rollback()
            print(traceback.print_exc())
            print(e.__traceback__.tb_lineno)
    return return_data


#   存库 吨油操作成本明细
class year_dvpunit_oilprodcost():

    def updata(self, data):
        try:
            session_pub.query(md_pub.TbYearDvpunitOilprodcost). \
                filter(md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name == data["DvpUnit_Name"],
                       md_pub.TbYearDvpunitOilprodcost.Year == data["Year"]).update(
                {
                    md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name: data["DvpUnit_Name"],
                    md_pub.TbYearDvpunitOilprodcost.Year: data["Year"],
                    md_pub.TbYearDvpunitOilprodcost.Oil_OprCost: data["Oil_OprCost"],
                    md_pub.TbYearDvpunitOilprodcost.FuelCost: data["FuelCost"],
                    md_pub.TbYearDvpunitOilprodcost.Power_Cost: data["Power_Cost"],
                    md_pub.TbYearDvpunitOilprodcost.FluidDispose_Cost: data["FluidDispose_Cost"],
                    md_pub.TbYearDvpunitOilprodcost.OilDisplaceAgent_Cost: data["OilDisplaceAgent_Cost"],
                    md_pub.TbYearDvpunitOilprodcost.NGLRecovery_Cost: data["NGLRecovery_Cost"],
                    md_pub.TbYearDvpunitOilprodcost.Transp_Cost: data["Transp_Cost"],
                    md_pub.TbYearDvpunitOilprodcost.WellTest_Cost: data["WellTest_Cost"],
                    md_pub.TbYearDvpunitOilprodcost.Other_Cost: data["Other_Cost"],
                    md_pub.TbYearDvpunitOilprodcost.Depre_Cost: data["Depre_Cost"],
                    md_pub.TbYearDvpunitOilprodcost.Remark: data["Remark"]
                }
            )
            session_pub.commit()
            print('更新输入数据成功')
            return True
        except BaseException:
            print('更新输入数据失败')
            session_pub.rollback()
            return False

    def delete(self, id):
        try:
            session_pub.query(md_pub.TbYearDvpunitOilprodcost) \
                .filter(md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name == id["DvpUnit_Name"],
                        md_pub.TbYearDvpunitOilprodcost.Year == id["Year"]).delete()
            session_pub.commit()
            print('删除数据成功')
            return True
        except BaseException:
            print('删除数据失败')
            session_pub.rollback()
            return False

    def add(self, data):
        new_data = md_pub.TbYearDvpunitOilprodcost(
            DvpUnit_Name=data["DvpUnit_Name"],
            Year=data["Year"],
            Oil_OprCost=data["Oil_OprCost"],
            FuelCost=data["FuelCost"],
            Power_Cost=data["Power_Cost"],
            FluidDispose_Cost=data["FluidDispose_Cost"],
            OilDisplaceAgent_Cost=data["OilDisplaceAgent_Cost"],
            NGLRecovery_Cost=data["NGLRecovery_Cost"],
            Transp_Cost=data["Transp_Cost"],
            WellTest_Cost=data["WellTest_Cost"],
            Other_Cost=data["Other_Cost"],
            Depre_Cost=data["Depre_Cost"],
            Remark=data["Remark"]
        )
        try:
            session_pub.add(new_data)
            session_pub.commit()
            print('存入数据成功')
            session_pub.close()
            return True
        except BaseException:
            print('存入数据失败')
            session_pub.rollback()
            return False

# hzc改


def oilprodcost(cost_data):
    return_data = list()
    for data in cost_data:
        try:
            session_pub.query(md_pub.TbYearDvpunitOilprodcost).filter(
                md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilprodcost.Year == data['Year']
            ).delete()
            add_data = md_pub.TbYearDvpunitOilprodcost(
                DvpUnit_Name=data["DvpUnit_Name"],
                Year=data["Year"],
                Oil_OprCost=data["Oil_OprCost"],
                FuelCost=data["FuelCost"],
                Power_Cost=data["Power_Cost"],
                FluidDispose_Cost=data["FluidDispose_Cost"],
                OilDisplaceAgent_Cost=data["OilDisplaceAgent_Cost"],
                NGLRecovery_Cost=data["NGLRecovery_Cost"],
                Transp_Cost=data["Transp_Cost"],
                WellTest_Cost=data["WellTest_Cost"],
                Other_Cost=data["Other_Cost"],
                Depre_Cost=data["Depre_Cost"],
                Remark=data["Remark"]
            )
            session_pub.add(add_data)
            session_pub.commit()
            return_data.append(
                {
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'result': True
                }
            )

        except Exception as e:
            return_data.append(
                {
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'result': False
                }
            )
            session_pub.rollback()
            print(traceback.print_exc())
            print(e.__traceback__.tb_lineno)
    return return_data

def delete_dvp_oil_result(delete):
    result = list()
    for data in delete:
        try:
            db_data = session_pub.query(md_pub.TbYearDvpunitOilstimResult).filter(
                md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimResult.Year == data['Year'],
                md_pub.TbYearDvpunitOilstimResult.StimType_Name == data['StimType_Name'],
            )
            if db_data.first():
                db_data.delete()
                session_pub.commit()
                result.append({
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'StimType_Name': data['StimType_Name'],
                    'result': True,
                    'message': '删除成功',
                })
            else:
                result.append({
                    'DvpUnit_Name': data['DvpUnit_Name'],
                    'Year': data['Year'],
                    'StimType_Name': data['StimType_Name'],
                    'result': False,
                    'message': '无此数据',
                })
        except Exception as e:
            print(e, e.__traceback__.tb_lineno)
            session_pub.rollback()
            result.append({
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': False,
                'message': '异常',
            })
    return result

# 存库更新部分数据
def input_updata(input_upd):
    try:
        session_pro.query(md_pro.TbUserprojDvpunitOilstimevalInput).\
            filter(md_pro.TbUserprojDvpunitOilstimevalInput.User_ID == input_upd["User_ID"],
                   md_pro.TbUserprojDvpunitOilstimevalInput.Proj_ID == input_upd["Proj_ID"]).update(
            {
                md_pro.TbUserprojDvpunitOilstimevalInput.DvpUnit_Name: input_upd["DvpUnit_Name"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Year: input_upd["Year"],
                md_pro.TbUserprojDvpunitOilstimevalInput.StimType_Name: input_upd["StimType_Name"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Stim_WellNumber_Valid: input_upd["Stim_WellNumber_Valid"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Stim_WellValidRatio: input_upd["Stim_WellValidRatio"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Stim_ValidPeriod_Average: input_upd["Stim_ValidPeriod_Average"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Stim_IncreOil_Average: input_upd["Stim_IncreOil_Average"],
                md_pro.TbUserprojDvpunitOilstimevalInput.StimCost_Average: input_upd["StimCost_Average"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Oil_OprCost: input_upd["Oil_OprCost"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Oil_Price: input_upd["Oil_Price"],
                md_pro.TbUserprojDvpunitOilstimevalInput.StimOil_CommodityRatio: input_upd["StimOil_CommodityRatio"],
                md_pro.TbUserprojDvpunitOilstimevalInput.OilSale_TotalTaxRatio: input_upd["OilSale_TotalTaxRatio"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Money_Discount: input_upd["Money_Discount"],
                md_pro.TbUserprojDvpunitOilstimevalInput.Remark: input_upd["Remark"],
            }
        )
        session_pro.commit()
        print('更新输入数据成功')
        return True
    except BaseException:
        print('更新输入数据失败')
        return False

# 存库删除部分数据


def input_del(id):
    try:
        session_pro.query(md_pro.TbUserprojDvpunitOilstimevalInput)\
            .filter(md_pro.TbUserprojDvpunitOilstimevalInput.User_ID == id["User_ID"],
                    md_pro.TbUserprojDvpunitOilstimevalInput.Proj_ID == id["Proj_ID"]).delete()
        session_pro.commit()
        print('删除数据成功')
        return True
    except BaseException:
        print('删除数据失败')
        return False


# 将所有信息存库
"""
单元名称，年度，措施类别，有效井次，有效率，平均有效期，平均措施增油,
平均作业成本，操作吨油成本，平均油价，原油商品率，平均销售税费率，资金贴现率，备注
"""


def input_add(input_data):
    # hzc改
    return_data = list()
    for data in input_data:
        try:

            session_pub.query(md_pub.TbYearDvpunitOilstimResult).filter(
                md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimResult.Year == data['Year'],
                md_pub.TbYearDvpunitOilstimResult.StimType_Name == data['StimType_Name']
            ).delete()
            add_result_data = md_pub.TbYearDvpunitOilstimResult(
                DvpUnit_Name=data['DvpUnit_Name'],
                Year=data['Year'],
                StimType_Name=data['StimType_Name'],
                Stim_WellNumber_Valid=data['Stim_WellNumber_Valid'],
                Stim_WellValidRatio=data['Stim_WellValidRatio'],
                Stim_ValidPeriod_Average=data['Stim_ValidPeriod_Average'],
                Stim_IncreOil_Average=data['Stim_IncreOil_Average'],
                Remark=data['Remark']
            )
            session_pub.query(md_pub.TbYearDvpunitOilstimCost).filter(
                md_pub.TbYearDvpunitOilstimCost.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilstimCost.Year == data['Year'],
                md_pub.TbYearDvpunitOilstimCost.StimType_Name == data['StimType_Name']
            ).delete()
            add_cost_data = md_pub.TbYearDvpunitOilstimCost(
                DvpUnit_Name=data['DvpUnit_Name'],
                Year=data['Year'],
                StimType_Name=data['StimType_Name'],
                Stim_Cost=data['Stim_Cost'],
                Remark=data['Remark']
            )
            session_pub.query(md_pub.TbYearDvpunitOilprice).filter(
                md_pub.TbYearDvpunitOilprice.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitOilprice.Year == data['Year'],
            ).delete()
            add_price_data = md_pub.TbYearDvpunitOilprice(
                DvpUnit_Name=data['DvpUnit_Name'],
                Year=data['Year'],
                Oil_CommodityRatio=data['Oil_CommodityRatio'],
                Oil_Price=data['Oil_Price'],
                Remark=data['Remark']
            )
            session_pub.query(md_pub.TbYearDvpunitDiscount).filter(
                md_pub.TbYearDvpunitDiscount.DvpUnit_Name == data['DvpUnit_Name'],
                md_pub.TbYearDvpunitDiscount.Year == data['Year'],
            ).delete()
            add_discount_data = md_pub.TbYearDvpunitDiscount(
                DvpUnit_Name=data['DvpUnit_Name'],
                Year=data['Year'],
                Money_Discount=data['Money_Discount'],
                Remark=data['Remark']
            )
            session_pub.add(add_result_data)
            session_pub.add(add_cost_data)
            session_pub.add(add_price_data)
            session_pub.add(add_discount_data)
            session_pub.commit()
            return_data.append({
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': True
            })

        except Exception as e:
            return_data.append({
                'DvpUnit_Name': data['DvpUnit_Name'],
                'Year': data['Year'],
                'StimType_Name': data['StimType_Name'],
                'result': False,
            })
            session_pub.rollback()
            print(traceback.print_exc())
            continue
    return return_data


# 获取所有单元名称和措施类别，按年份排序
def get_name_year():
    try:
        all_info = session_pub.query(
            md_pub.TbYearDvpunitOilstimResult.DvpUnit_Name,
            md_pub.TbYearDvpunitOilstimResult.StimType_Name,
            md_pub.TbYearDvpunitOilstimResult.Year
        ).order_by(
            md_pub.TbYearDvpunitOilstimResult.Year
        ).all()
        all_info = [dict(zip(item.keys(), item))for item in all_info]
        dvp = set()
        stimtype = set()
        year = set()
        for item in all_info:
            dvp.add(item['DvpUnit_Name'])
            stimtype.add(item['StimType_Name'])
            year.add(item['Year'])

        dvpunit_name_list = list(dvp)
        stimtype_name_list = list(stimtype)

        all_dvp_in_db = session_pub.query(
            md_pub.TbDvpunitbaisc.DvpUnit_ID,
            md_pub.TbDvpunitbaisc.DvpUnit_Name,
            md_pub.TbDvpunitbaisc.Belong_DvpUnit_Name,
            md_pub.TbDvpunitbaisc.DvpUnit_Rank
        ).filter(
            md_pub.TbDvpunitbaisc.DvpUnit_Name.in_(dvpunit_name_list)
        ).all()
        all_dvp_in_db = [dict(zip(type.keys(), type))
                         for type in all_dvp_in_db]
        dvp_tree = list()
        dvp_list = sorted(all_dvp_in_db, key=lambda x: x["DvpUnit_Rank"])
        while len(dvp_list):
            item = dvp_list[0]
            temp_dict = dict()
            temp_dict.update({
                'id': item['DvpUnit_ID'],
                'title': item['DvpUnit_Name'],
            })
            child = dvpunit_name_tree(dvp_list, item['DvpUnit_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            dvp_tree.append(temp_dict)
            del_name = set()
            new_list = list()
            del_name.add(item['DvpUnit_Name'])
            for x in dvp_list:
                if x['Belong_DvpUnit_Name'] in del_name or x['DvpUnit_Name'] in del_name:
                    del_name.add(x['DvpUnit_Name'])
                else:
                    new_list.append(x)
            dvp_list = new_list

        all_stimtype_in_db = session_pub.query(
            md_pub.TbOilwellstimType.StimType_Name,
            md_pub.TbOilwellstimType.Belong_StimType_Name,
            md_pub.TbOilwellstimType.StimType_Rank,
            md_pub.TbOilwellstimType.StimType_ID
        ).filter(
            md_pub.TbOilwellstimType.StimType_Name.in_(stimtype_name_list)
        ).all()
        all_stimtype_in_db = [dict(zip(type.keys(), type))
                              for type in all_stimtype_in_db]
        stim_type_tree = list()
        stimtype_list = sorted(all_stimtype_in_db,key=lambda x: x["StimType_Rank"])
        while len(stimtype_list):
            item = stimtype_list[0]
            temp_dict = dict()
            temp_dict.update({
                'id': item['StimType_ID'],
                'title': item['StimType_Name'],
            })
            child = stimtype_tree(stimtype_list, item['StimType_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            stim_type_tree.append(temp_dict)
            del_name = set()
            new_list = list()
            del_name.add(item['StimType_Name'])
            for x in stimtype_list:
                if x['Belong_StimType_Name'] in del_name or x['StimType_Name'] in del_name:
                    del_name.add(x['StimType_Name'])
                else:
                    new_list.append(x)
            stimtype_list = new_list
        all_year = sorted(list(year))
        year = None
        if all_year:
            year = list()
            year.append(all_year[0])
            year.append(all_year[len(all_year) - 1])
        return dvp_tree, stim_type_tree, year
    except Exception:
        session_pub.rollback()
        print(traceback.print_exc())
        return False, False, False

# 获取所有单元名称和措施类别，按年份排序


def get_name_year_public():
    try:
        units = session_pub.query(
            md_pub.TbDvpunitbaisc.DvpUnit_Name,
            md_pub.TbDvpunitbaisc.Belong_DvpUnit_Name,
            md_pub.TbDvpunitbaisc.DvpUnit_ID,
            md_pub.TbDvpunitbaisc.DvpUnit_Rank).all()
        units = [dict(zip(unit.keys(), unit)) for unit in units]
        # print("now", units)
        stim_type = session_pub.query(md_pub.TbOilwellstimType.StimType_Name,
                                      md_pub.TbOilwellstimType.Belong_StimType_Name,
                                      md_pub.TbOilwellstimType.StimType_Rank,
                                      md_pub.TbOilwellstimType.StimType_ID).all()
        stim_type = [dict(zip(type.keys(), type)) for type in stim_type]
        years = session_pub.query(
            md_pub.TbYearDvpunitOilstimCost.Year).distinct().all()
        # 处理措施数据，生成树形结构
        stim_type_tree = list()
        for type in stim_type:
            temp_dic = dict()
            if type['StimType_Rank'] == '1':
                temp_dic['id'] = type['StimType_ID']
                temp_dic['title'] = type['StimType_Name']
                temp_dic['rank'] = type['StimType_Rank']
                children = create_tree(stim_type, type['StimType_Name'], "措施")
                if children:
                    temp_dic['children'] = children
                stim_type_tree.append(temp_dic)
        # 获取年份数据
        year_list = list()
        if len(years):
            for year in years:
                year_list.append(year[0])

        # 处理单元数据
        units_tree = list()
        for unit in units:
            temp_dic = dict()
            if unit['DvpUnit_Rank'] == "1":
                temp_dic['title'] = unit['DvpUnit_Name']
                temp_dic['id'] = unit['DvpUnit_ID']
                temp_dic['rank'] = unit['DvpUnit_Rank']
                children = create_tree(units, unit['DvpUnit_Name'], "开发单元")
                if children:
                    temp_dic['children'] = children
                units_tree.append(temp_dic)
        response_data = {
            "DvpUnit": units_tree if units_tree else None,
            "StimType": stim_type_tree if stim_type_tree else None,
            "Year": year_list if year_list else None
        }

        return response_data
    except Exception as e:
        session_pub.rollback()
        print(e, e.__traceback__.tb_lineno)
        return(e.__traceback__.tb_lineno)


def create_tree(total_data, parent_unit_name, data_type):
    if data_type == "开发单元":
        belong_str, id_str, name_str, rank_str = "Belong_DvpUnit_Name", "DvpUnit_ID", "DvpUnit_Name", "DvpUnit_Rank"
    elif data_type == "措施":
        belong_str, id_str, name_str, rank_str = "Belong_StimType_Name", "StimType_ID", "StimType_Name", "StimType_Rank"
    tree = []
    try:
        for data in total_data:
            if data[rank_str] == "1":
                continue
            temp_dic = dict()
            if data[belong_str] == parent_unit_name:
                temp_dic['id'] = data[id_str]
                temp_dic['title'] = data[name_str]
                temp_dic['rank'] = data[rank_str]
                children = create_tree(total_data, data[name_str], data_type)
                if children:
                    temp_dic['children'] = children
                tree.append(temp_dic)
        return tree
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)


# BP神经网络预测 读取规划方案、单元名称、起止年度
def read_Plan_Unit_Year():
    try:
        DvpUnit_OilStim_PlanProj_Name = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name).all()

        units = session_pub.query(
            md_pub.TbDvpunitbaisc.DvpUnit_Name,
            md_pub.TbDvpunitbaisc.Belong_DvpUnit_Name,
            md_pub.TbDvpunitbaisc.DvpUnit_ID).all()
        units = [dict(zip(unit.keys(), unit)) for unit in units]

        StimPlanProj_StartYear = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_StartYear).filter().all()
        StimPlanProj_EndYear = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_EndYear).filter().all()
        planName = []
        unitName = create_tree(units, None, "开发单元")
        Year = []
        for i in range(len(DvpUnit_OilStim_PlanProj_Name)):
            planName.append(DvpUnit_OilStim_PlanProj_Name[i][0])

        for i in range(len(StimPlanProj_StartYear)):
            Year.append(int(StimPlanProj_StartYear[i][0]))
        for i in range(len(StimPlanProj_EndYear)):
            Year.append(int(StimPlanProj_EndYear[i][0]))
        minYear = Year[0]
        maxYear = Year[0]
        for i in range(len(Year)):
            if Year[i] < minYear:
                minYear = Year[i]
            if Year[i] > maxYear:
                maxYear = Year[i]
        return {
            "planName": planName if planName else None,
            "unitName": unitName if unitName else None,
            "yearRange": [minYear, maxYear]
        }
    except Exception as e:
        print(traceback.print_exc())
        print(e.__traceback__.tb_lineno)
        return False


def get_projId_by_user_id(user_id):
    try:
        input_project_inf = session_pro.query(
            md_pro.TbUserprojWellInput.Proj_ID
        ).filter(
            md_pro.TbUserprojWellInput.User_ID == user_id
        ).all()

        out_project_inf = session_pro.query(
            md_pro.TbUserprojWellOut.Proj_ID
        ).filter(
            md_pro.TbUserprojWellOut.User_ID == user_id
        ).all()

        return input_project_inf, out_project_inf
    except Exception as e:
        print('查找数据失败', e)
        session_pro.rollback()
        return False


def read_pri_well(all_well_name, stimType_Name, start_year, end_year):
    try:
        db_datas = session_pub.query(
            md_pub.TbWellbasic.Belong_DvpUnit_Name
        ).filter(
            md_pub.TbWellbasic.Well_Name.in_(all_well_name)
        ).all()
        dvpUnit_Name = list()
        for data in db_datas:
            dvpUnit_Name.append(data[0])
        temp_results = session_pub.query(
            md_pub.TbYearDvpunitOilstimCost.DvpUnit_Name,
            md_pub.TbYearDvpunitOilstimCost.StimType_Name,
            md_pub.TbYearDvpunitOilstimCost.Year
        ).filter(
            md_pub.TbYearDvpunitOilstimCost.DvpUnit_Name.in_(dvpUnit_Name),
            md_pub.TbYearDvpunitOilstimCost.Year <= end_year,
            md_pub.TbYearDvpunitOilstimCost.Year >= start_year,
            md_pub.TbYearDvpunitOilstimCost.StimType_Name.in_(stimType_Name)
        ).all()
        temp_result_dict = [dict(zip(result.keys(), result))
                            for result in temp_results]
        result_data = None
        if temp_result_dict:
            results = list()
            for result in temp_result_dict:
                temp_result = session_pub.query(
                    md_pub.TbYearDvpunitOilstimCost.DvpUnit_Name,
                    md_pub.TbYearDvpunitOilstimCost.StimType_Name,
                    md_pub.TbYearDvpunitOilstimCost.Year,

                    md_pub.TbOilwellstimResult.Well_Name,  # 井号
                    md_pub.TbOilwellstimResult.Stim_Date,  # 措施日期
                    md_pub.TbOilwellstimResult.Stim_ValidPeriod,  # 有效期
                    md_pub.TbOilwellstimResult.Stim_IncreOil,  # 措施增油量
                    md_pub.TbOilwellstimResult.Remark,

                    md_pub.TbYearDvpunitOilstimCost.Stim_Cost,  # 作业费用

                    md_pub.TbYearDvpunitOilprice.Oil_CommodityRatio,  # 原油商品率
                    md_pub.TbYearDvpunitOilprice.Oil_Price,  # 原油价格

                    md_pub.TbYearDvpunitOilprodcost.Oil_OprCost,  # 操作吨油成本
                    md_pub.TbYearDvpunitOilprodcost.FuelCost,  # 燃料费
                    md_pub.TbYearDvpunitOilprodcost.Power_Cost,  # 动力费
                    md_pub.TbYearDvpunitOilprodcost.FluidDispose_Cost,  # 油气水处理费
                    md_pub.TbYearDvpunitOilprodcost.OilDisplaceAgent_Cost,  # 驱油物注入费
                    md_pub.TbYearDvpunitOilprodcost.NGLRecovery_Cost,  # 轻烃回收费
                    md_pub.TbYearDvpunitOilprodcost.Transp_Cost,  # 运输费
                    md_pub.TbYearDvpunitOilprodcost.WellTest_Cost,  # 测井试井费
                    md_pub.TbYearDvpunitOilprodcost.Other_Cost,  # 其他直接费用
                    md_pub.TbYearDvpunitOilprodcost.Depre_Cost,  # 折旧费用

                    md_pub.TbYearDvpunitOilsalecostRatio.OilSale_TotalTaxRatio,  # 销售税费率
                    md_pub.TbYearDvpunitOilsalecostRatio.AddedValue_TaxRatio,  # 增值税率
                    md_pub.TbYearDvpunitOilsalecostRatio.Resource_TaxRatio,  # 资源税率
                    md_pub.TbYearDvpunitOilsalecostRatio.EduAdd_TaxRatio,  # 教育附加税费
                    md_pub.TbYearDvpunitOilsalecostRatio.CityDvpAdd_TaxRate,  # 城市维护建设税率
                    md_pub.TbYearDvpunitOilsalecostRatio.Income_TaxRate,  # 所得税率
                    md_pub.TbYearDvpunitOilsalecostRatio.ManageCost_Ratio,  # 其他费率

                    md_pub.TbYearDvpunitDiscount.Money_Discount  # 资金贴现率
                ).filter(

                    md_pub.TbOilwellstimResult.Well_Name.in_(all_well_name),
                    md_pub.TbOilwellstimResult.StimType_Name == result['StimType_Name'],
                    md_pub.TbOilwellstimResult.Stim_Date >= start_year,
                    md_pub.TbOilwellstimResult.Stim_Date <= end_year,

                    md_pub.TbYearDvpunitOilstimCost.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilstimCost.Year == result['Year'],
                    md_pub.TbYearDvpunitOilstimCost.StimType_Name == result['StimType_Name'],

                    md_pub.TbYearDvpunitOilprice.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilprice.Year == result['Year'],

                    md_pub.TbYearDvpunitOilprodcost.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilprodcost.Year == result['Year'],

                    md_pub.TbYearDvpunitOilsalecostRatio.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitOilsalecostRatio.Year == result['Year'],

                    md_pub.TbYearDvpunitDiscount.DvpUnit_Name == result['DvpUnit_Name'],
                    md_pub.TbYearDvpunitDiscount.Year == result['Year']
                ).first()
                if temp_result:
                    results.append(temp_result)
            result_data = [dict(zip(result.keys(), result))
                           for result in results]
        return result_data
    except Exception as e:
        print('输入数据查找数据失败', e)
        session_pub.rollback()
        return False
